import pandas as pd

df = pd.read_csv("static/data/qi_dian_book.csv")
df_word_cloud = pd.read_csv("static/data/word_cloud.csv")


# 定义函数month_ticket_top10，用于获取月票前10名
def month_ticket_top10():
    # 按照month_ticket字段降序排序，取前10行
    df_ticket_top10 = df.sort_values('month_ticket', ascending=False).head(10)
    # 获取title字段值，转换为列表
    name = df_ticket_top10['title'].values.tolist()
    # 获取month_ticket字段值，转换为列表
    value = df_ticket_top10['month_ticket'].values.tolist()
    # 返回name和value列表
    return {
        'name': name,
        'value': value
    }


def ratio_status():
    df_ratio_status = df.groupby('status').count()['title'].reset_index()
    ratio_status_data = [{'value': i[1], 'name': i[0]} for i in df_ratio_status.values]
    return {
        'data': ratio_status_data
    }


def category_level():
    category = df.groupby(['category']).count().index.tolist()
    category_data = [
        {
            'name': i,
            'value': len(df[df['category'] == i]),
            'children': [
                {
                    'name': j[0],
                    'value': j[1]
                } for j in df[df['category'] == i]
                .groupby('secondary_category')
                .count()['title']
                .reset_index()
                .values
            ]
        } for i in category
    ]
    return {
        'data': category_data
    }


def funnel_data():
    df_status = df[df['status'] == '连载']
    df_month_ticket = df_status[df_status['month_ticket'] > 1000]
    df_category = df_month_ticket[df_month_ticket['category'] == '玄幻']
    df_secondary_category = df_category[df_category['secondary_category'] == '东方玄幻']
    data = [
        {
            'value': 100,
            'name': '全部'
        },
        {
            'value': round(len(df_status) / len(df) * 100, 2),
            'name': '连载'
        },
        {
            'value': round(len(df_month_ticket) / len(df) * 100, 2),
            'name': '月票>1000'
        },
        {
            'value': round(len(df_category) / len(df) * 100, 2),
            'name': '玄幻'
        },
        {
            'value': round(len(df_secondary_category) / len(df) * 100, 2),
            'name': '东方玄幻'
        }
    ]
    return {
        'data': data
    }


def word_cloud_data():
    data = [{'name': i[0], 'value': i[1]} for i in df_word_cloud.head(50).values]
    return {
        'data': data
    }
